package utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.BeansException;
import org.springframework.jdbc.core.JdbcTemplate;

import config.Constant;




public class SqlUtils {
	
	public static String tables_select_mysql  = "show tables";
	public static String tables_select_sqlserver  = "select name from sysobjects where xtype='U' order by name;";
	public static String tables_select_oracle     = "select table_name from user_tables order by table_name asc";
	
	public static String constraint_en_sqlserver  = "select  'ALTER TABLE '  + b.name +  ' CHECK CONSTRAINT ' +  a.name +';' as  启用约束  from  sysobjects  a ,sysobjects  b   where  a.xtype ='f' and  a.parent_obj = b.id";
	public static String constraint_en_oracle     = "select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'";
	public static String constraint_dis_sqlserver = "select  'ALTER TABLE '  + b.name +  ' NOCHECK CONSTRAINT ' +  a.name +';' as  禁用约束  from  sysobjects  a ,sysobjects  b   where  a.xtype ='f' and  a.parent_obj = b.id";
	public static String constraint_dis_oracle    = "select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'";
	public static List<String> getTables(String dbType) {
		try {
			List<String> tablelist = new ArrayList<String>();
			Connection conn = ((JdbcTemplate) Constant.AppContext.getBean("jdbcTemplate")).getDataSource().getConnection();
			Statement stmt = conn.createStatement();
			ResultSet resultSet = null;
	
			//表格列表
			if(dbType.equals("sqlserver")){
				resultSet = stmt.executeQuery(tables_select_sqlserver);
			}
			else if(dbType.equals("oracle")){
				resultSet = stmt.executeQuery(tables_select_oracle);
			}
			else if(dbType.equals("mysql")){
				resultSet = stmt.executeQuery(tables_select_mysql);
			}
			while (resultSet.next()) {
				String t = resultSet.getString(1);
				t = t.toLowerCase();
				tablelist.add(t);
			}
			return tablelist;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return null;
		
	}
	
	public static void script(String dbType) {
		
		List<String> constraint_en_list = new ArrayList<String>();
		List<String> constraint_dis_list = new ArrayList<String>();
		List<String> tablelist = new ArrayList<String>();
		try {
		
			Connection conn = ((JdbcTemplate) Constant.AppContext.getBean("jdbcTemplate")).getDataSource().getConnection();
			Statement stmt = conn.createStatement();
			ResultSet resultSet = null;

			//表格列表
			if(dbType.equals("sqlserver")){
				resultSet = stmt.executeQuery(tables_select_sqlserver);
			}
			else if(dbType.equals("oracle")){
				resultSet = stmt.executeQuery(tables_select_oracle);
			}
			
			while (resultSet.next()) {
				String t = resultSet.getString(1);
				t = t.toLowerCase();
				tablelist.add(t);
			}
			
			//启用约束语句
			if(dbType.equals("sqlserver")){
				resultSet = stmt.executeQuery(constraint_en_sqlserver);
			}
			else if(dbType.equals("oracle")){
				resultSet = stmt.executeQuery(constraint_en_oracle);
			}
			
			while (resultSet.next()) {
				String t = resultSet.getString(1);
				t = t.toLowerCase();
				constraint_en_list.add(t);
			}
			//禁用约束语句
			if(dbType.equals("sqlserver")){
				resultSet = stmt.executeQuery(constraint_dis_sqlserver);
			}
			else if(dbType.equals("oracle")){
				resultSet = stmt.executeQuery(constraint_dis_oracle);
			}
			while (resultSet.next()) {
				String t = resultSet.getString(1);
				t = t.toLowerCase();
				constraint_dis_list.add(t);
			}
		} catch (BeansException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		
		for(String a : constraint_dis_list){
			System.out.println(a);
		}
		for(String a : constraint_en_list){
			System.out.println(a);
		}
		for(String a : tablelist){
			System.out.println(a);
		}
	}
}
